package br.com.appclassificados.dao;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import br.com.appclassificados.helpers.ConnectionFactory;
import br.com.appclassificados.model.SubCategoria;

public class SubCategoriaDao implements CRUD<SubCategoria>{
	
	
	//ATRIBUTOS
	private Connection con;
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	
	// TODO Construtores
    public SubCategoriaDao(){
    	this.con = ConnectionFactory.getConnection().getConn();
    }	
	
	//METODOS	
    //Testado com Exito, 14:00, 18/10/2013, Maicon D.
	@Override
	public boolean insert(SubCategoria obj) {
		try{
            PreparedStatement ps = this.con.prepareStatement("insert into sub_categoria values ( ? , ? , ? )");
            ps.setInt(1, getChave());
            ps.setInt(2, obj.getCategoriaId());
            ps.setString(3, obj.getNome());
            ps.execute();
            ps.close();
            this.con.close();                         
            return true;            
        }catch(Exception e){
            return false;
        }   
	}

	//Testado com Exito, 14:00, 18/10/2013, Maicon D.
	@Override
	public boolean delete(SubCategoria obj) {
		try{
            PreparedStatement ps = this.con.prepareStatement("delete from sub_categoria where sub_categoriaid = ?");
            ps.setInt(1, obj.getSubCategoriaId());
            ps.executeUpdate();
            ps.close();
            this.con.close();                         
            return true;            
        }catch(Exception e){
            return false;
        } 
	}

	//Testado com Exito, 14:00, 18/10/2013, Maicon D.
	@Override
	public boolean update(SubCategoria obj) {
		try{
            PreparedStatement ps = this.con.prepareStatement("update sub_categoria set categoriaid = ?, nome = ?  where sub_categoriaid = ?");            
            ps.setInt(1, obj.getCategoriaId());
            ps.setString(2, obj.getNome());
            ps.setInt(3, obj.getSubCategoriaId());
            ps.executeUpdate();
            ps.close();
            this.con.close();                         
            return true;            
        }catch(Exception e){
            return false;
        }
	}

	//Testado com Exito, 14:00, 18/10/2013, Maicon D.
	@Override
	public List<SubCategoria> readList() {
		try{
            PreparedStatement ps = this.con.prepareStatement("select sub_categoriaid, categoriaid, nome from sub_categoria");                                    
            this.rs = ps.executeQuery();            
            
            List<SubCategoria> ls = new ArrayList<SubCategoria>();
            
            while(this.rs.next()){
                SubCategoria s = new SubCategoria(this.rs.getInt("sub_categoriaid"), this.rs.getInt("categoriaid"), this.rs.getString("nome"));
                ls.add(s);                
            }
            return ls;
        }catch(Exception e){
            return null;
        }
	}

	//Testado com Exito, 14:00, 18/10/2013, Maicon D.
	@Override
	public SubCategoria search(int id) {
		try{
            PreparedStatement ps = this.con.prepareStatement("select categoriaid, nome from sub_categoria where sub_categoriaid = ?");                        
            ps.setInt(1, id);
            this.rs = ps.executeQuery();
            
            if(this.rs.next()){
                SubCategoria s = new SubCategoria(id, this.rs.getInt("categoriaid"), this.rs.getString("nome"));
                return s;
            }else
                return null;
        }catch(Exception e){
            return null;
        }
	}

	//Testado com Exito, 14:00, 18/10/2013, Maicon D.
	@Override
	public SubCategoria search(String value) {
		try{
	         PreparedStatement ps = this.con.prepareStatement("select sub_categoriaid, categoriaid, nome from sub_categoria where upper(nome) like ?");                        
	         ps.setString(1, "%"+value.toUpperCase()+"%");
	         this.rs = ps.executeQuery();
	            
	         if(this.rs.next()){
	             SubCategoria s = new SubCategoria(this.rs.getInt("sub_categoriaid"), this.rs.getInt("categoriaid"), this.rs.getString("nome"));
	             return s;
	         }else
	        	 return null;
	     }catch(Exception e){
	    	 return null;
	     }
	}

	@Override
	public int getChave() {
		try{
	         PreparedStatement ps = this.con.prepareStatement("select max(sub_categoriaid) from sub_categoria");                        
	         this.rs = ps.executeQuery();
	            
	         if(this.rs.next()){
	        	 return (this.rs.getInt("max(sub_categoriaid)") + 1);
	         }else
	        	 return 0;
	     }catch(Exception e){
	    	 return 0;
	     }
	}

}
